use std::str::FromStr;
use anyhow::anyhow;
use anyhow::Ok;
use anyhow::Result;
use chrono::Local;
use chrono::Utc;
use log::debug;
use log::info;
use log::trace;
use surrealdb::opt::PatchOp;
use surrealdb::sql::Thing;

use crate::model::kline::Klt;
use crate::model::security::Security;
use crate::model::todo;
use crate::model::todo::Todo;
use crate::model::todo::TodoStatus;
use crate::model::todo::TodoType;
use crate::model::FudaRecord;

use super::FUDB;
use super::FUHLC;

pub async fn upsert_all(todos: &Vec<Todo>) -> Result<()> {
    let start = Utc::now();
    let last_updated = format!("{}", FUHLC.new_timestamp());
    let query = FUDB.query(
        r#"
        FOR $v IN $todos { 
            INSERT INTO todo (id,name,type_,parameters,status,plan_at,reference,last_updated) 
            VALUES ($v.id, $v.name, $v.type_, $v.parameters, $v.status, $v.plan_at, $v.reference, $last_updated)
            ON DUPLICATE KEY UPDATE
                name=$v.name,
                type_=$v.type_,
                parameters=$v.parameters,
                status=$v.status,
                plan_at=$v.plan_at,
                reference=$v.reference,
                last_updated=$last_updated;
        }
        "#
    )
        .bind(("todos", todos))
        .bind(("last_updated", last_updated));

    let _rs = query.await?;
    let end = Utc::now();
    info!("(todo::upsert_all) rs= {:?} spend time={:?}", _rs, end - start);

    Ok(())
}

///
/// update or create a todo record
///
/// @param todo: todo's basic information
///
pub async fn upsert(todo_id: &str, todo: &todo::Todo) -> Result<Option<FudaRecord>> {
    let old_todo: Option<todo::Todo> = FUDB.select(("todo", todo_id)).await?;

    let mut updated: Option<FudaRecord> = None;
    let mut to_update = false;
    let timestamp = format!("{}", FUHLC.new_timestamp());
    // Update a record with a specific ID
    let mut patch = FUDB.update(("todo", todo_id)).patch(
        PatchOp::replace("/last_updated", timestamp)
    );

    match old_todo {
        Some(Todo { id: Some(t), .. }) => {
            updated = Some(FudaRecord { id: t });
        }
        _ => {
            patch = patch.patch(PatchOp::replace("/name", &todo.name));
            patch = patch.patch(PatchOp::replace("/type_", &todo.type_));
            to_update = true;
        }
    }

    if let Some(v) = &todo.parameters {
        patch = patch.patch(PatchOp::replace("/parameters", &v));
        to_update = true;
    }

    if let Some(v) = &todo.status {
        patch = patch.patch(PatchOp::replace("/status", &v));
        to_update = true;
    }

    if let Some(v) = &todo.created_at {
        patch = patch.patch(PatchOp::replace("/created_at", &v));
        to_update = true;
    }

    if let Some(v) = &todo.plan_at {
        patch = patch.patch(PatchOp::replace("/plan_at", &v));
        to_update = true;
    }

    if let Some(v) = &todo.started_at {
        patch = patch.patch(PatchOp::replace("/started_at", &v));
        to_update = true;
    }

    if let Some(v) = &todo.ended_at {
        patch = patch.patch(PatchOp::replace("/ended_at", &v));
        to_update = true;
    }

    if let Some(v) = &todo.failures {
        patch = patch.patch(PatchOp::replace("/failures", &v));
        to_update = true;
    }

    if to_update {
        updated = patch.await.map_err(|err| anyhow!(err))?;
    }

    Ok(updated)
}

/// find todo
///
/// todo_id: &str
///
///
pub async fn find(todo_id: &str) -> Result<Option<Todo>> {
    let id = Thing::from(("todo", todo_id));

    let todo: Option<Todo> = FUDB.query(r#"SELECT * FROM $id"#).bind(("id", id)).await?.take(0)?;

    Ok(todo)
}
///
/// delete todo by it's id , e.g. SH.STK.600519
///
pub async fn delete(todo_id: &str) -> Result<Option<Todo>> {
    let rs: Option<Todo> = FUDB.delete(("todo", todo_id)).await?;
    Ok(rs)
}

/// select craw tasks
///
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select(
    types: &Option<Vec<TodoType>>,
    status: &Option<TodoStatus>,
    is_after_plan_at: bool,
    pi: u16,
    pn: u16
) -> Result<Vec<todo::Todo>> {
    let start_time = Utc::now();

    let type_cond = if let Some(tps) = types {
        let tps_str = tps
            .iter()
            .map(|tp| format!("'{}'", tp))
            .collect::<Vec<_>>()
            .join(",");
        format!(" type_ in [{}]", tps_str.to_uppercase())
    } else {
        "".to_string()
    };
    let where_ = match (types, status, is_after_plan_at) {
        (None, None, true) => format!("WHERE {} plan_at < $now", type_cond),
        (Some(_), None, true) => format!("WHERE  {}  AND plan_at < $now", type_cond),
        (None, Some(_), true) =>
            format!("WHERE  {}  status = $status AND plan_at < $now", type_cond),
        (Some(_), Some(_), true) =>
            format!("WHERE  {}  AND status = $status AND plan_at < $now", type_cond),
        (None, None, false) => format!(r#"  {}  "#, type_cond),
        (Some(_), None, false) => format!("WHERE  {}  ", type_cond),
        (None, Some(_), false) => format!("WHERE  {}  status = $status ", type_cond),
        (Some(_), Some(_), false) => format!("WHERE  {}  AND status = $status", type_cond),
    };

    let start = pi * pn;

    let q_str = &format!(
        "SELECT * FROM todo {} ORDER BY last_updated ASC LIMIT {} START {} ",
        where_,
        pn,
        start
    );

    let now = Local::now();
    info!("(todo::select) {}, status: {:?}, now: {:?}", q_str, status, now);
    // let q_str = "select * from todo WHERE type_  in ['CRAWLKZZLIST','CRAWLKZZKLINES']  limit 3";

    // Fetch todo data within the specified date range using bind variables
    let mut query = FUDB.query(q_str);

    if let Some(t_) = &status {
        query = query.bind(("status", t_));
    }

    if is_after_plan_at {
        query = query.bind(("now", now));
    }

    trace!("(todo::select) query={:?}", query);

    let mut response = query.await?;

    trace!("\n(todo::select) response= {:?}\n", response);

    let values: Vec<Todo> = response.take(0)?;
    let end_time = Utc::now();
    trace!("\n(todo::select) @{:?} spent time = {:?}", end_time, end_time - start_time);

    Ok(values)
}

pub fn get_security_id_by_parameter(todo: &Todo) -> Result<String> {
    // get security
    let security_id: Option<String> = todo.get_parameter("security_id");
    match security_id {
        Some(v) => { Ok(v) }
        None => Err(anyhow!("parameter security_id is None")),
    }
}

pub fn get_todo_string_parameter_by(todo: &Todo, name: &str) -> Result<String> {
    // get security
    let security_id: Option<String> = todo.get_parameter(name);
    match security_id {
        Some(v) => { Ok(v) }
        None => Err(anyhow!("parameter {name} is None")),
    }
}

pub fn get_klt_by_parameter(todo: &Todo) -> Result<Klt> {
    // get klt
    let klt_: Option<String> = todo.get_parameter("klt");
    let klt: Result<crate::model::kline::Klt> = match klt_ {
        Some(v) => {
            let rs = Klt::from_str(&v);
            rs
        }
        None => Err(anyhow!("parameter klt is None")),
    };
    klt
}

pub fn get_limit_by_parameter(todo: &Todo) -> Result<u32> {
    // get limit
    let limit_: Option<String> = todo.get_parameter("limit");
    let limit = match limit_ {
        Some(v) => {
            let rs = v.parse::<u32>().map_err(|e| anyhow!(e));
            rs
        }
        None => Err(anyhow!("parameter limit is None")),
    };
    limit
}

pub fn get_last_updated_by_parameter(todo: &Todo) -> Result<String> {
    // get last_updated
    let last_updated_: Option<String> = todo.get_parameter("last_updated");
    let last_updated = match last_updated_ {
        Some(v) => { Ok(v) }
        None => Err(anyhow!("parameter last_updated is None")),
    };
    last_updated
}

// Ok(v) => Ok(v),
// Err(e) => Err(e),

pub async fn get_security_by_parameter(todo: &Todo) -> Result<Security> {
    // get security
    let security_id: Option<String> = todo.get_parameter("security_id");
    let security: Result<Security> = match security_id {
        Some(v) => {
            let rs = super::security::find(&v).await;
            rs
        }
        None => Err(anyhow!("parameter security_id is None")),
    };
    security
}

#[cfg(test)]
mod test {
    use chrono::{ Duration, Local };

    use crate::db;
    use crate::db::FUHLC;
    use crate::model::todo;
    use log::trace;

    #[tokio::test]
    async fn test_upsert_todo() {
        let _ = db::init().await.unwrap();
        for i in 0..10 {
            let todo = todo::Todo {
                type_: if i == 0 {
                    Some(todo::TodoType::CrawlKzzList)
                } else {
                    Some(todo::TodoType::CrawlStockList)
                },
                status: Some(todo::TodoStatus::Pending),
                name: Some(format!("todo_name_{}", i)),
                plan_at: if i % 2 == 0 {
                    Some(Local::now())
                } else {
                    Some(Local::now() + Duration::days(1))
                },
                ..Default::default()
            };

            let id = todo::Todo::id_from(&["id1", "id2", &i.to_string()]);
            // Update a todo record with a specific id
            let res = db::todo::upsert(id.as_str(), &todo).await;

            trace!("res {:?}", res);
        }
    }

    /// Need to call first: test_update_todo_from_eastmoney to update todos for "513130"
    #[tokio::test]
    async fn test_select_todos() {
        let _ = db::init().await.unwrap();

        let result = db::todo::select(
            &Some(vec![todo::TodoType::CrawlKzzList, todo::TodoType::CrawlKzzKlines]),
            &Some(todo::TodoStatus::Pending),
            // &None,
            false,
            0,
            10
        ).await;

        // trace!("result= {:?}\n", result);
        assert_eq!(result.is_ok(), true);

        if let Ok(values) = &result {
            for (index, value) in values.iter().enumerate() {
                trace!("[{:02}]= {:?} ", index, value);
            }
        }
    }
    #[tokio::test]
    async fn test_timestamp() {
        let timestamp = format!("{}", FUHLC.new_timestamp());

        trace!("timestamp= {}", timestamp);
    }
}
